package com.saic.parse.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.saic.parse.IImport;
import com.saic.parse.IImportAmong;

public class ImportSeriesPrice implements IImportAmong {

	@Override
	public void importExcel(Connection connBartack,Connection connDataParse) throws Exception {
		Statement statementconnBartack = connBartack.createStatement();
		statementconnBartack.execute("delete from t_velseries_price");
		ResultSet result = statementconnBartack.executeQuery("select cityId,velSeriesId,min(guiding_price),max(guiding_price),min(current_price),min(guiding_price-current_price),max(guiding_price-current_price) from t_velmodel_price group by cityId,velSeriesId");
		while(result.next()){
			
			Integer cityid = result.getInt(1);					// 	城市id
			Integer isbgain = 0;								// 	是否支持砍价
			Integer velseriesId = result.getInt(2);;			// 	车系id
			Integer islowprice = 0;								// 	是否支持降价
			Integer isprmt = 0;									// 	是否支持优惠
			double min_guiding_price = result.getDouble(3);		//	车系下车型最低指导价格				
			double max_guiding_price = result.getDouble(4);		//	车系下车型最高指导价格
			double min_current_price = result.getDouble(5);		//	车系下车型最低现价
			double min_preferential_Price = result.getDouble(6);	//	车系下车型最低优惠价
			double max_preferential_Price = result.getDouble(7);	// 	车系下车型最高优惠价
		    result.getInt(1);
			
			
			PreparedStatement prepareStatement = connBartack.prepareStatement("insert into t_velseries_price(cityid,isbgain,velseriesId,islowprice,min_guiding_price,max_guiding_price,min_current_price,min_preferential_Price,max_preferential_Price)values(?,?,?,?,?,?,?,?,?)");
			prepareStatement.setInt(1, cityid);
			prepareStatement.setInt(2, isbgain);
			prepareStatement.setInt(3, velseriesId);
			prepareStatement.setInt(4, islowprice);

			prepareStatement.setDouble(5, min_guiding_price);

			prepareStatement.setDouble(6, max_guiding_price);

			prepareStatement.setDouble(7, min_current_price);
			prepareStatement.setDouble(8, min_preferential_Price < 0 ? 0 : min_preferential_Price);

			prepareStatement.setDouble(9, max_preferential_Price < 0 ? 0 : max_preferential_Price);
			prepareStatement.execute();
			prepareStatement.close();
		}
		
	}
	public static void main(String[] args)throws Exception {

		
		String dataParseSql = "jdbc:mysql://192.168.22.70:3306/dataparser?"
	      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
		Class.forName("com.mysql.jdbc.Driver");
		Connection connDataParse = DriverManager.getConnection(dataParseSql);
		
		String bartackSql = "jdbc:mysql://192.168.22.70:3306/bartack?"
		      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
			Class.forName("com.mysql.jdbc.Driver");

		Connection connBartack = DriverManager.getConnection(bartackSql);
		
		ImportSeriesPrice imp = new ImportSeriesPrice();
		connDataParse.setAutoCommit(false);
		connBartack.setAutoCommit(false);
		imp.importExcel(connBartack,connDataParse);
		connDataParse.commit();
		connBartack.commit();
		
	
	}

}
